﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.Agent;
using Microsoft.SqlServer.Management.Smo.Broker;
using Microsoft.SqlServer.Management.Smo.Mail;
using System.Linq;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Common;
using System.Data.Linq;
using System.Data.Common;
using System.ComponentModel;
using System.Data.Linq.Mapping;
using System.Collections.ObjectModel;
using System.Linq.Expressions;
using System.Reflection;




namespace POS_NEW
{

    internal class AccessDataLayer
    {
       
       
        // Fields
        public static string database;
        public static string pass;
        public static string server;
        public static string user;
       
        public static string ConnectionString()
        {
           
            return ("server = RAD2; database =POS_NEW; uid =sa; password = phuc123; ");
        }
        public static DataContext DataClass()
        {
            try
            {
            
                DataContext Data = new DataContext(ConnectionString());
                return Data;
            }
            catch (SqlException e)
            {
                MessageBox.Show(e.ToString());
            }
            return null;
        }
        public static DataSet Execute(string sql, CommandType commandtype, params object[] par)
        {

            DataSet ds = new DataSet();
            DataContext ctx = DataClass();
            ctx.Connection.Open();
            using (DbCommand cmd = ctx.Connection.CreateCommand())
            {
                cmd.CommandText = sql;
                cmd.CommandType = commandtype;
                for (int i = 0; i < par.Length; i += 2)
                {
                    DbParameter parameter = new SqlParameter(par[i].ToString(), par[i + 1]);
                    cmd.Parameters.Add(parameter);
                }
                
                new SqlDataAdapter((SqlCommand)cmd).Fill(ds);

                ctx.Connection.Close();
                return ds;

            }
            //SqlConnection connection = new SqlConnection(ConnectionString());
            //connection.Open();
            //SqlCommand selectCommand = new SqlCommand(sql, connection)
            //{
            //    CommandType = commandtype
            //};
            //for (int i = 0; i < par.Length; i += 2)
            //{
            //    SqlParameter parameter = new SqlParameter(par[i].ToString(), par[i + 1]);
            //    selectCommand.Parameters.Add(parameter);
            //}
            //DataSet dataSet = new DataSet();
            //new SqlDataAdapter(selectCommand).Fill(dataSet);
            //connection.Close();

        }
        public static DataSet Execute(string sql, CommandType commandtype,object output,ref object get, params object[] par)
        {
         
            DataSet ds = new DataSet();
            DataContext ctx = DataClass();
            ctx.Connection.Open();
            using (DbCommand cmd = ctx.Connection.CreateCommand())
            {
                cmd.CommandText = sql;
                cmd.CommandType = commandtype;
                for (int i = 0; i < par.Length; i += 2)
                {
                    DbParameter parameter = new SqlParameter(par[i].ToString(), par[i + 1]);
                    cmd.Parameters.Add(parameter);
                }
                DbParameter paraOutput = new SqlParameter();
                paraOutput.ParameterName = output.ToString();
                paraOutput.DbType = DbType.Object;
                paraOutput.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(paraOutput);
                new SqlDataAdapter((SqlCommand)cmd).Fill(ds);
                get = paraOutput.Value.ToString();
                ctx.Connection.Close();
                return ds;
              
            }
            //SqlConnection connection = new SqlConnection(ConnectionString());
            //connection.Open();
            //SqlCommand selectCommand = new SqlCommand(sql, connection)
            //{
            //    CommandType = commandtype
            //};
            //for (int i = 0; i < par.Length; i += 2)
            //{
            //    SqlParameter parameter = new SqlParameter(par[i].ToString(), par[i + 1]);
            //    selectCommand.Parameters.Add(parameter);
            //}
            //DataSet dataSet = new DataSet();
            //new SqlDataAdapter(selectCommand).Fill(dataSet);
            //connection.Close();

        }

        public static SqlDataAdapter ExecuteChart(string sql, CommandType commandtype,params object[] par)
        {
            SqlConnection connection = new SqlConnection(ConnectionString());
            connection.Open();
            SqlCommand selectCommand = new SqlCommand(sql, connection)
            {
                CommandType = commandtype
            };
            for (int i = 0; i < par.Length; i += 2)
            {
                SqlParameter parameter = new SqlParameter(par[i].ToString(), par[i + 1]);
                selectCommand.Parameters.Add(parameter);
            }
            DataSet set = new DataSet();
            SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
            connection.Close();
            return adapter;
        }
        public static DataTable ExecuteDT(string sql, CommandType commandtype, params object[] par)
        {
            //SqlConnection connection = new SqlConnection(ConnectionString());
            //connection.Open();
            //SqlCommand selectCommand = new SqlCommand(sql, connection)
            //{
            //    CommandType = commandtype
            //};
            //for (int i = 0; i < par.Length; i += 2)
            //{
            //    SqlParameter parameter = new SqlParameter(par[i].ToString(), par[i + 1]);
            //    selectCommand.Parameters.Add(parameter);
            //}
            //DataSet dataSet = new DataSet();
            //new SqlDataAdapter(selectCommand).Fill(dataSet);
            //connection.Close();
            //return dataSet.Tables[0];
            DataSet ds = new DataSet();
            DataContext ctx = DataClass();
            ctx.Connection.Open();
            using (DbCommand cmd = ctx.Connection.CreateCommand())
            {
                cmd.CommandText = sql;
                cmd.CommandType = commandtype;
                for (int i = 0; i < par.Length; i += 2)
                {
                    DbParameter parameter = new SqlParameter(par[i].ToString(), par[i + 1]);
                    cmd.Parameters.Add(parameter);
                }
                new SqlDataAdapter((SqlCommand)cmd).Fill(ds);

                ctx.Connection.Close();
                return ds.Tables[0];

            }
        }
        public static DataTable ExecuteDT(string sql,object output,ref object get, CommandType commandtype, params object[] par)
        {
            //SqlConnection connection = new SqlConnection(ConnectionString());
            //connection.Open();
            //SqlCommand selectCommand = new SqlCommand(sql, connection)
            //{
            //    CommandType = commandtype
            //};
            //for (int i = 0; i < par.Length; i += 2)
            //{
            //    SqlParameter parameter = new SqlParameter(par[i].ToString(), par[i + 1]);
            //    selectCommand.Parameters.Add(parameter);
            //}
            //DataSet dataSet = new DataSet();
            //new SqlDataAdapter(selectCommand).Fill(dataSet);
            //connection.Close();
            //return dataSet.Tables[0];
            DataSet ds = new DataSet();
            DataContext ctx = DataClass();
            ctx.Connection.Open();
            using (DbCommand cmd = ctx.Connection.CreateCommand())
            {
                cmd.CommandText = sql;
                cmd.CommandType = commandtype;
                for (int i = 0; i < par.Length; i += 2)
                {
                    DbParameter parameter = new SqlParameter(par[i].ToString(), par[i + 1]);
                    cmd.Parameters.Add(parameter);
                }
                DbParameter paraOutput = new SqlParameter();
                paraOutput.ParameterName = output.ToString();
                paraOutput.DbType = DbType.Object;
                paraOutput.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(paraOutput);
                new SqlDataAdapter((SqlCommand)cmd).Fill(ds);
                get = paraOutput.Value.ToString();
                ctx.Connection.Close();
                return ds.Tables[0];

            }
        }
        public static int ExecuteNonQuery(string sql, CommandType commandtype, params object[] par)
        {
            DataContext ctx = DataClass();
            ctx.Connection.Open();
            using (DbCommand cmd = ctx.Connection.CreateCommand())
            {
                cmd.CommandText = sql;
                cmd.CommandType = commandtype;
               for (int i = 0; i < par.Length; i += 2)
                {

                    DbParameter parameter = cmd.CreateParameter();
                    parameter.ParameterName = par[i].ToString();
                    parameter.Value = par[i + 1];
                    cmd.Parameters.Add(parameter);
                }
                int num = cmd.ExecuteNonQuery();
                ctx.Connection.Close();
                return num;
              

            }
        }
        public static int ExecuteNonQuery(string sql,string outputname,ref string outputvalue, CommandType commandtype, params object[] par)
        {
            DataContext ctx = DataClass();
            ctx.Connection.Open();
            using (DbCommand cmd = ctx.Connection.CreateCommand())
            {
                cmd.CommandText = sql;
                cmd.CommandType = commandtype;
                for (int i = 0; i < par.Length; i += 2)
                {
                    DbParameter parameter = cmd.CreateParameter();
                    parameter.ParameterName = par[i].ToString();
                    parameter.Value = par[i + 1];
                    cmd.Parameters.Add(parameter);
                }
                DbParameter paraOutput = cmd.CreateParameter();
                paraOutput.ParameterName = outputname;
                paraOutput.DbType = DbType.String;
                paraOutput.Direction = ParameterDirection.Output;
                paraOutput.Size = 50;
                cmd.Parameters.Add(paraOutput);

                int num = cmd.ExecuteNonQuery();
                outputvalue = paraOutput.Value.ToString();
                ctx.Connection.Close();
                return num;
            }
        }

        public static DataSet ExecuteTableTemp(string sql, DataTable tabletemp, string paraTable)
        {
            SqlConnection connection = new SqlConnection(ConnectionString());
            connection.Open();
            SqlCommand selectCommand = new SqlCommand(sql, connection)
            {
                CommandType = CommandType.StoredProcedure
            };
            SqlParameter parameter = new SqlParameter
            {
                ParameterName = paraTable,
                SqlDbType = SqlDbType.Structured,
                Value = tabletemp
            };
            selectCommand.Parameters.Add(parameter);
            DataSet dataSet = new DataSet();
            new SqlDataAdapter(selectCommand).Fill(dataSet);
            connection.Close();
            return dataSet;
        }
        public static void BackupDatabase(string destinationPath)
        {
            try
            {
                Backup backup = new Backup
                {
                    Action = BackupActionType.Database,
                    Database = "QLKhoBanHang_BVTV"
                };
                BackupDeviceItem item = new BackupDeviceItem(destinationPath, DeviceType.File);
                ServerConnection serverConnection = new ServerConnection(server, user, pass);
                Server srv = new Server(serverConnection);
                Database database = srv.Databases["QLKhoBanHang_BVTV"];
                backup.Devices.Add(item);
                backup.ExpirationDate = DateTime.Now.AddDays(45.0);
                backup.SqlBackup(srv);
            }
            catch (ServiceRequestException)
            {
                MessageBox.Show("Lỗi Tạo File Backup. Vui L\x00f2ng Li\x00ean Hệ Admin. ĐT: 0989708460 (Ph\x00fac)");
            }
        }
        public static void RestoreDatabase(string databaseName, string filePath, string serverName, string userName, string password, string dataFilePath, string logFilePath)
        {
            Restore restore = new Restore();
            BackupDeviceItem item = new BackupDeviceItem(filePath, DeviceType.File);
            restore.Devices.Add(item);
            restore.Database = "QLKhoBanHang_BVTV";
            ServerConnection serverConnection = new ServerConnection(server, user, pass);
            Server srv = new Server(serverConnection);
            Database database = srv.Databases["QLKhoBanHang_BVTV"];
            restore.Action = RestoreActionType.Database;
            string physicalFileName = dataFilePath + "QLKhoBanHang_BVTV.mdf";
            string str2 = logFilePath + "QLKhoBanHang_BVTV_Log.ldf";
            database = srv.Databases["QLKhoBanHang_BVTV"];
            RelocateFile file = new RelocateFile("QLKhoBanHang_BVTV", physicalFileName);
            restore.RelocateFiles.Add(new RelocateFile("QLKhoBanHang_BVTV", physicalFileName));
            restore.RelocateFiles.Add(new RelocateFile("QLKhoBanHang_BVTV_log", str2));
            restore.ReplaceDatabase = true;
       
            restore.PercentCompleteNotification = 10;
          
            restore.SqlRestore(srv);
            srv.Databases["QLKhoBanHang_BVTV"].SetOnline();
            srv.Refresh();
        }


        #region --------------------------LINQ--------------------------------
        public static T SelectByPK<T>(String id) where T : class
        {
            try
            {
                using (DataContext context = DataClass())
                {
                    // get the table by the type passed in
                    var table = context.GetTable<T>();

                    // get the metamodel mappings (database to
                    // domain objects)
                    MetaModel modelMap = table.Context.Mapping;

                    // get the data members for this type
                    ReadOnlyCollection<MetaDataMember> dataMembers
                    = modelMap.GetMetaType(typeof(T)).DataMembers;

                    // find the primary key field name
                    // by checking for IsPrimaryKey
                    string pk =
                    (dataMembers.Single<MetaDataMember>(m =>
                    m.IsPrimaryKey)).Name;

                    // return a single object where the id argument
                    // matches the primary key field value
                    return table.SingleOrDefault<T>(delegate(T t)
                    {
                        String memberId =
                                t.GetType().GetProperty(pk).GetValue(t,
                                                            null).ToString();
                        return memberId.ToString() == id.ToString();
                    });
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
        public static IList<T> SelectAll<T>() where T : class
        {
            try
            {
                using (DataContext context = DataClass())
                {
                    // get the table matching the type
                    // and return all of it as typed list
                    var table = context.GetTable<T>().ToList<T>();
                    return table;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
        public static Type GetPrimaryKeyType<T>() where T : class
        {
            try
            {
                using (DataContext context = DataClass())
                {
                    // get the table by the type passed in
                    var table = context.GetTable<T>();

                    // get the metamodel mappings (database to
                    // domain objects)
                    MetaModel modelMapping = table.Context.Mapping;

                    // get the data members for this type                   
                    ReadOnlyCollection<MetaDataMember> dataMembers
                    = modelMapping.GetMetaType(typeof(T))
                    .DataMembers;

                    // find the primary key and return its type
                    return (dataMembers.Single<MetaDataMember>(m =>
                    m.IsPrimaryKey)).Type;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
        public static string GetPrimaryKeyName<T>() where T : class
        {
            try
            {
                using (DataContext context = DataClass())
                {
                    // get the table by the type passed in
                    var table = context.GetTable<T>();

                    // get the metamodel mappings (database to
                    // domain objects)
                    MetaModel modelMapping = table.Context.Mapping;

                    // get the data members for this type
                    ReadOnlyCollection<MetaDataMember> dataMembers
                    = modelMapping.GetMetaType(typeof(T))
                    .DataMembers;

                    // find the primary key field and return its
                    // name
                    return (dataMembers.Single<MetaDataMember>(m
                    => m.IsPrimaryKey)).Name;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
        public static void Insert<T>(T item) where T : class
        {
            try
            {
                // get the approapriate table by type and then
                // insert the passed in object and submit
                // that change to the data context
                using (DataContext context = DataClass())
                {
                    // get the table by the type passed in
                    var table = context.GetTable<T>();

                    // pass in the object with insert on submit
                    // and then submit changes
                    table.InsertOnSubmit(item);
                    context.SubmitChanges();
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
        public static void Update<T>(T item) where T : class
        {
            try
            {
                // create a new instance of the object
                Object newObj = Activator.CreateInstance(typeof(T), new object[0]);

                PropertyDescriptorCollection originalProps = TypeDescriptor.GetProperties(item);

                // set the new object to match the passed in object
                foreach (PropertyDescriptor currentProp in originalProps)
                {
                    if (currentProp.Attributes[typeof(System.Data.Linq.Mapping.ColumnAttribute)] != null)
                    {
                        object val = currentProp.GetValue(item);
                        currentProp.SetValue(newObj, val);
                    }
                }

                // attach the new object to a new data context and
                // call submit changes on the context
                using (DataContext context = DataClass())
                {
                    var table = context.GetTable<T>();
                    table.Attach((T)newObj, true);
                    context.SubmitChanges();
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
        public static void Remove<T>(T item) where T : class
        {
            try
            {
                // create a new instance of an object with the
                // type matching the passed object's type
                Type tType = item.GetType();
                Object newObj = Activator.CreateInstance(tType, new object[0]);

                // get the properties for the passed in object
                PropertyDescriptorCollection originalProps = TypeDescriptor.GetProperties(item);

                // copy over the content of the passed in data
                // to the new object of the same type â€“
                // this gives us an object
                // that is not tied to any existing data context
                foreach (PropertyDescriptor currentProp in originalProps)
                {
                    if (currentProp.Attributes[typeof(System.Data.Linq.Mapping.ColumnAttribute)] != null)
                    {
                        object val = currentProp.GetValue(item);
                        currentProp.SetValue(newObj, val);
                    }
                }

                // to work with disconnected data, attach the new                 
                // object to the table, call delete
                // on submit, and then submit changes
                using (DataContext context = DataClass())
                {
                   
                    var table = context.GetTable<T>();
                    table.Attach((T)newObj, true);
                    table.DeleteOnSubmit((T)newObj);
                    context.SubmitChanges();
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
       

    

        #endregion

        
    }


}
